package sanrenxing.right.dao;

import sanrenxing.right.entity.UserEntity;
import sanrenxing.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * 用户数据库操作类
 */
public class UserDao {
    /**
     * 增加用户信息
     * @param userEntity 用户信息的对象
     * @return boolean类型 增加成功 则返回true 否则返回false
     */
    public boolean  insertUser(UserEntity userEntity){
        boolean flag=false;
        Connection con= null;
        PreparedStatement pst = null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        String sql="INSERT INTO USER VALUES (?,?,?,?)";

            pst = con.prepareStatement(sql);
            pst.setInt(1,userEntity.getUserId());
            pst.setString(2,userEntity.getUserName());
            pst.setString(3,userEntity.getUserPwd());
            pst.setInt(4,userEntity.getRoleId());
            int result=pst.executeUpdate();
            flag = result>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(null,pst,con);
        }

        return flag;
    }
    /**
     * 修改用户信息
     * @param userEntity 用户信息的对象
     * @return boolean类型 增加成功 则返回true 否则返回false
     */
    public boolean  updateUser(UserEntity userEntity){
        boolean flag=false;
        Connection con= null;
        PreparedStatement pst = null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        String sql="UPDATE USER SET userName=?,userPwd=?" +
                ",roleId=? WHERE userId=?";
            pst = con.prepareStatement(sql);
            pst.setString(1,userEntity.getUserName());
            pst.setString(2,userEntity.getUserPwd());
            pst.setInt(3,userEntity.getRoleId());
            pst.setInt(4,userEntity.getUserId());

            int result=pst.executeUpdate();
            flag = result>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(null,pst,con);
        }

        return flag;
    }
    /**
     * 根据用户ID删除用户信息
     * @param userId 用户信息的用户ID
     * @return boolean类型 增加成功 则返回true 否则返回false
     */
    public boolean deleteUser(int userId){
        boolean flag=false;
        Connection con= null;
        PreparedStatement pst = null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        String sql="DELETE FROM USER WHERE userId=?";
            pst = con.prepareStatement(sql);
            pst.setInt(1,userId);
            int result=pst.executeUpdate();
            flag = result>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(null,pst,con);
        }

        return flag;
    }
    public String buildWhereSql(String userName){
        StringBuffer sqlWhere=new StringBuffer();
        if(userName !=null && !userName.equals(""))
        {
            sqlWhere.append(" and userName like '%" + userName + "%'");
        }

        return sqlWhere.toString();
    }

    /**
     * 查询所有用户信息
     * @param  userName 用户名 查询的条件
     * @param  page  页码 第几页
     * @param  pageSize 每页显示的条数
     * @return List<UserEntity> 用户信息的集合
     */
    public List<UserEntity> selectUser(String userName,int page ,int pageSize){
        List<UserEntity> userList =new ArrayList<>();
        Connection con= null;
        PreparedStatement pst = null;
        ResultSet rs=null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        //调用拼接条件的方法，返回具体的条件
        String sqlWhere= buildWhereSql(userName);
        // (page-1)*pageSize 该页的起始条数 例如每页显示10条 若要显示第二页，第二页的起始数是10
        String sql="select * from user where 1=1 " + sqlWhere + " limit "
                + (page-1)*pageSize + "," + pageSize;
            pst = con.prepareStatement(sql);
             rs = pst.executeQuery();
            while (rs.next()) {
                UserEntity userEntity=new UserEntity();
                userEntity.setUserId(rs.getInt("userId"));
                userEntity.setUserName(rs.getString("userName"));
                userEntity.setUserPwd(rs.getString("userPwd"));
                userEntity.setRoleId(rs.getInt("roleId"));
                userList.add(userEntity);//添加到集合
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(rs,pst,con);
        }

        return  userList;
    }

    /**
     * 计算总条数的方法
     * @param userName  用户名 条件
     * @return 返回了总记录数
     */
    public int count(String userName){
        int count=0;
        Connection con= null;
        PreparedStatement pst = null;
        ResultSet rs=null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        //调用拼接条件的方法，返回具体的条件
        String sqlWhere= buildWhereSql(userName);
        String sql="select count(*) from user where 1=1 " + sqlWhere;
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            if(rs.next())
            {
                count=rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.close(rs,pst,con);
        }
        return count;
    }

    public static void main(String[] args) {
        UserEntity userEntity=new UserEntity(6,"chenjia","777777",1);
     /*  boolean flag= new UserDao().updateUser(userEntity);
        System.out.println("flag=" + flag);*/
      //  new UserDao().insertUser(userEntity);
      //  boolean flag=new UserDao().deleteUser(4);
        //System.out.println(flag);
    /*    List<UserEntity> userList = new UserDao().selectUser("s",1,5);
        for(UserEntity user:userList){
            System.out.println(user);
        }*/
        int count=new UserDao().count("c");

    }
}
